package com.immunology.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.immunology.core.BaseDao;
import com.immunology.core.BaseTransformer;
import com.immunology.core.GenericDao;
import com.immunology.database.DbColumns;
import com.immunology.database.transformers.PatientTransformer;
import com.immunology.model.Patient;

public class PatientDao extends BaseDao<Patient> implements GenericDao<Patient> {

	public PatientDao(BaseTransformer<Patient> transformer) {
		super(transformer, new String("PATIENT"));
	}

	@Override
	public void insert(Patient patient) {
		Connection connection = connectionManager.getConnection();
		StringBuilder query = new StringBuilder();
		query.append("INSERT INTO ");
		query.append(tableName);
		query.append(" (FIRST_NAME, MIDDLE_NAME, LAST_NAME, GENDER, BIRTHDAY, "
				+ "COUNTRY, REGION, DISTRICT, CITY, STREET, HOUSE, FLAT) ");
		query.append(" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
		try {
			// patient.setId(getNextId());
			PreparedStatement pstmt = (PreparedStatement) connection
					.prepareStatement(query.toString());
			// pstmt.setInt(DbColumns.FIRST_COLUMN, patient.getId());
			pstmt.setString(DbColumns.FIRST_COLUMN, patient.getFirstName());
			pstmt.setString(DbColumns.SECOND_COLUMN, patient.getMiddleName());
			pstmt.setString(DbColumns.THIRD_COLUMN, patient.getLastName());
			pstmt.setString(DbColumns.FOURTH_COLUMN, patient.getGender()
					.value());
			pstmt.setDate(DbColumns.FIFTH_COLUMN, patient.getBirthday());
			pstmt.setString(DbColumns.SIXTH_COLUMN, patient.getCountry());
			pstmt.setString(DbColumns.SEVENTH_COLUMN, patient.getRegion());
			pstmt.setString(DbColumns.EIGHTH_COLUMN, patient.getDistrict());
			pstmt.setString(DbColumns.NINTH_COLUMN, patient.getCity());
			pstmt.setString(DbColumns.TENTH_COLUMN, patient.getStreet());
			pstmt.setString(DbColumns.ELEVENTH_COLUMN, patient.getHouse());
			pstmt.setString(DbColumns.TWELFTH_COLUMN, patient.getFlat());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connectionManager.closeConnection(connection);
		}
	}

	public void update(int id, Patient obj) {
		// TODO Auto-generated method stub
	}

	public List<Patient> search(String text) throws SQLException {
		List<Patient> patient = new LinkedList<Patient>();
		Connection connection = connectionManager.getConnection();
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		StringBuilder textSearch = new StringBuilder();
		StringBuilder query = new StringBuilder();
		if (text == null) {
			query.append("SELECT * FROM ");
			query.append(tableName);
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(query.toString());

		} else {
			textSearch.append("%");
			textSearch.append(text);
			textSearch.append("%");
			query.append("SELECT * FROM ");
			query.append(tableName);
			query.append(" WHERE UPPER(FIRST_NAME) LIKE ? ");
			query.append("OR UPPER(MIDDLE_NAME) LIKE ? OR UPPER(LAST_NAME) LIKE ?");
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(query.toString());
			preparedStatement.setString(1, textSearch.toString());
			preparedStatement.setString(2, textSearch.toString());
			preparedStatement.setString(3, textSearch.toString());
		}
		resultSet = preparedStatement.executeQuery();
		while (resultSet.next()) {
			patient.add(new PatientTransformer().transformTo(resultSet));

		}
		return patient;
	}

	public int getLastIdByPatient() {
		Connection connection = connectionManager.getConnection();
		int id = 0;
		StringBuilder query = new StringBuilder();
		query.append("SELECT id FROM ");
		query.append(tableName);
		query.append(" ORDER BY id");
		try {
			Statement statement = connection.createStatement();
			ResultSet set = statement.executeQuery(query.toString());
			while (set.next()) {
				id = set.getInt(DbColumns.FIRST_COLUMN);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			connectionManager.closeConnection(connection);
		}
		return id;
	}

}
